
import glob
import os
import re
import string
import sys
import pepe.p_txt.bigtxt as bt

n = '\x0D\x0A'

def create_table_mssql(globmask='*METADATA.txt'):
    '''Create MsSQL table from oracle metadata file
    
    '''
    for f in glob.glob(globmask):

        fname = string.split(f, '\x5C')[-1]
        tname = string.split(fname, '.')[0]
        tname = re.sub('_METADATA', '', tname)
        o = open(tname + '.mssql.sql', 'wb')

        data_section = False
        create_table = n+ 'create table [import].%s (' % (tname)
        insert_into_a = 'insert into [import].<new table>'
        insert_into_a += n + '          ( '
        insert_into_b = n + '     select '

        lino = 0
        for line in bt.lines(f):
            line = re.sub(n, '', line)
            line = re.sub('\x0A', '', line)

            if data_section == True:
                if line.strip() <> '':
                    cname = line[1:20].strip()
                    create_table += ' %s nvarchar(255),%s' % (cname, '')
                    insert_into_a += '[%s], ' % (cname, )
                    insert_into_b += '[Column %s], ' % (str(lino), )
                    lino += 1
            if re.sub('^Key Field Name', 'x', line[:14]) == 'x':
                data_section = True
 
        o.write(create_table[:-1]) # remove last coma
        o.write(')' + n + 'go' + n)
        o.write(insert_into_a[:-2] + ')') # remove last coma
        
        insert_into_b = insert_into_b[:-2] # remove last coma
        insert_into_b += n + '       from <old_table>' + n
        o.write(insert_into_b + 'go' + n + n)
        
        o.close()

def examine_oracle_metadata(f, fmask='METADATA.txt'):
    """Extract how many rows were exported from METADATA Oracle file

    Example of log file:
                                                       Dynamic Extract Tool - Extract Report                                                
         
        Table: BKPF                                                                                                          Ran by: E-KBABU
         
        Data file # of records: 33.456                                                                                                      
        Data file: /var/opt/archstage/CPO/prd/BKPF_DE00_2002.txt                                                                            
        System 46C Prog: Z9_CIT_ONLINE_EXTRACTION - Vers. 20                                                                                
        Runtime from 08.11.2010 11:55:58 to 08.11.2010 12:21:46                                                                             
        Codepage                                                                                                                            
         
        Selections:                                                                                                                         
        Client:    001                                                                                                                      
        CompCode:  DE00,                                                                                                                    
        FiscalYear:2002,                                                                                                                    
        VKORG:     8300,                                                                                                                    
        KTOPL:     HPCA,                                                                                                                    

    """
    r = ''
    if os.path.exists(f) and re.sub('METADATA.txt', '', f[-12:]) == '':
        data = open(f, 'r').read()

        s = re.search("(Data file # of records: [\d. ]{1,})\x0A", data)
        if s: 
            r = str(s.group(1))

    r = re.sub('\x0D\x0A', '', r)
    r = re.sub('\x0A', '', r)
    r = r.strip()

    return {'metadata_txt_records': r}

if __name__ == "__main__":
    ''' '''
    if  len(sys.argv) == 2:
        create_table_mssql(sys.argv[1])
    elif  len(sys.argv) == 1:
        create_table_mssql()
    else:
        print """
        """
